<?php
	function f430($sql){
		//data handle instance
		//set request
		//define format
		//set format
		//define query string
		//process time
		//process fitler
		$dataHandle = new DataHandle();
		$dataHandle->setRequest("430");


		$action = getValue($_GET['action']);

		if ($action == "getItems"){
			$error = $dataHandle->processTime($startTime, $endTime);
			if($error == ""){
				//--------------------------------------------
				//fix for sql >= problem
				//we are using getDay function to increment
				//end time for one day
				$timeHandle = new TimeHandle();
				$temp2 = $endTime;
				$timeHandle->getDay($temp2, $temp1, $endTime);
				unset($timeHandle);
				//--------------------------------------------
				$filter = $dataHandle->processFilters($startTime, $endTime, "alm_PLCs","start_time");
				$query = getJSONQuery($filter);
				print($dataHandle->printJSON($sql, $query));
			}
			else
				print($error);
		}
		else if($action == "getYears"){
			$query = getYears();
			print($dataHandle->printJSON($sql, $query));
		}
		else{
			$temp = array();
			$temp['error'] = "430: Bad request";
			print(json_encode($temp));
		}

		unset($dataHandle);

	}

	function getJSONQuery(&$filter){
		$queryString = "
			SELECT
				ROW_NUMBER() 
        			OVER (ORDER BY count(*) desc) AS ID,
				CONVERT (CHAR(10), min(start_time),104) AS 'StartTime',
				CONVERT (CHAR(10), max(end_time),104) AS 'EndTime',
				group_name AS 'Group',
				tag_name AS 'Tag',
				description AS 'Description',
				LTRIM(STR(ROUND(SUM(CONVERT(float,duration)),0))) + ' ' + RIGHT(CONVERT(VARCHAR, CONVERT(datetime, SUM(CONVERT(float,duration)) , 108),8),12) as duration,
				count(start_time) as 'count'
			FROM	
				alm_PLCs";

		$queryString = $queryString.$filter;

		$queryString = $queryString." GROUP BY group_name, tag_name, description ";
		
		$oderByFiled = getValue($_GET['sortItem']);
		$oderByValue = getValue($_GET['ascDesc']);

		if($oderByFiled !== "" and $oderByValue !== "")
			$queryString = $queryString." order by ".$oderByFiled." ".$oderByValue;

		return $queryString;
	}
	function getYears(){
		$queryString = 
			"SELECT DISTINCT 
				DATEPART(year, start_time) as availableYear 
			FROM alm_PLCs 
			order by availableYear desc";
		return $queryString;
	}
?>